<?php
 class sql_experience{
    public $db_name="";
    public $host="localhost";
    public $usr="root";
    public $password="ephrael_faith";

    public function __construct($_name){
      $this->db_name=$_name;
    }

    public function __set_db_name($_name){
      $this->db_name=$_name;
    }

    public function __get_connexion_pdo(){
      $connexion_pdo=new PDO(
        'mysql:host='.$this->host.';dbname='.$this->db_name,
        $this->usr,
        $this->password
      );
      return $connexion_pdo;
    }

    public function __create_db(){
      $request_protected=array(":db_name"=>$this->db_name);
      try{
        $connexion_pdo=$this->__get_connexion_pdo();
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);        $requete="
          CREATE DATABASE `:db_name`
          DEFAULT CHARACTER SET utf8
          COLLATE utf8_unicode_ci;
        ";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute($request_protected);
        $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }      
    }


    public function writer_create_experience(&$_array_sql_obj){ 
      foreach($_array_sql_obj as &$obj){
        $obj->__set_experience($this);
        $obj->writer_create_table();
      }
    }

    public function writer_delete_experience(&$_array_sql_obj){
      foreach($_array_sql_obj as &$obj){
        $obj->writer_delete_table();
      }
    }

    public function writer_drop_create_experience(&$_array_sql_obj){
      foreach($_array_sql_obj as &$obj){
        $obj->writer_drop_table();
        $obj->writer_create_table();
      }
    }

    public function reader_experience(){
     $all_table=$this->reader_table();
     $array_obj=array();
     foreach($all_table as $table){
      $obj_name="sql_".strtolower($table[0]);
      $obj=new $obj_name($this);
      $array_obj[strtolower($obj_name)]=$obj;
     }
     return $array_obj; 
    }


    public function reader_table(){
      $array_res=array();
      try{
        $connexion_pdo=$this->__get_connexion_pdo();
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="SHOW TABLES;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();
        foreach($prepare_execute as $ligne) $array_res[]=$ligne;
        $connexion_pdo = null; //Appel au destructeur de l'objet PDO
        return $array_res;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }

  }

  class sql_object{
    protected $class_prefix="sql_";
    protected $id_prefix="id_";
    protected $experience;

    public function __construct($_exp){
      $this->experience=$_exp;
    }

    public function __set_experience($_exp){
      $this->experience=$_exp;
    }

    protected function __get_table_name(){
      return ucfirst($this->__get_short_class_name());
    }

    protected function __get_class_name(){
      return get_class($this);
    }

    protected function __get_primary_key_name(){
      return strtolower($this->id_prefix.$this->__get_table_name());
    }

    protected function __get_short_class_name(){
      //$class_name=$this->__get_class_name();
      $res=explode("_",$this->__get_class_name());
      return $res[1];
    }
 
   // array_value :
   // - clefs : nom_attribut
   // - valeurs : valeur à ajouter
   
    protected function build_insert_protected($_array_values){
      $str1=$str2='';
      $array_res=array();
      foreach($_array_values as $attr=>$val){
        $str1.=$attr.',';
        $str2.=':'.$attr.',';
        $array_res[":$attr"]=$val;
      }
      $str1='('.rtrim($str1,',').')';
      $str2='('.rtrim($str2,',').')';
      return array("$str1 VALUES $str2;",$array_res);
    }

    protected function build_where_protected($_conditions){
      $str1='';
      $array_res=array();
      foreach($_conditions as $c=>$val){
        $str1.=$c.' = :'.$c.' AND ';
        $array_res[':'.$c]=$val;
      }
      $str1=rtrim($str1,' AND ');
      return array($str1,$array_res);
    }

    protected function build_set_protected($_set){
      $str1='';
      $array_res=array();
      foreach($_set as $s=>$val){
        $str1.=$s." = :".$s.',';
        $array_res[':'.$s]=$val;
      }
      $str1=rtrim($str1,',');
      return array($str1,$array_res);
    }

    // values :
    // - clefs : nom_attribut
    // - valeurs : valeur à modifier 
    // condition :
    // - clefs : nom attribut
    // - valeurs : valeur à respecter

    public function update_values($_set,$_condition){
     $table_name=$this->__get_table_name();
     $request_condition=$this->build_where_protected($_condition);
     $request_set=$this->build_set_protected($_set);
     $request_str="
       UPDATE `$table_name`
       SET $request_set[0] 
       WHERE $request_condition[0];
     ";
     $request_protected=Tool_array::array_merge_key($request_set[1],$request_condition[1]);
     try{
       $connexion_pdo=$this->experience->__get_connexion_pdo();
       $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       $prepare_execute=$connexion_pdo->prepare($request_str);
       $prepare_execute->execute($request_protected);
       $res=$connexion_pdo->lastInsertId();
       $connexion_pdo = null; //Appel au destructeur de l'objet PDO
       return $res;
     }
     catch(PDOException $erreur){
      echo $erreur->getMessage();
     }
    }


    public function update_array_values(&$_array_set){
     $table_name=$this->__get_table_name();
     try{
       $connexion_pdo=$this->experience->__get_connexion_pdo();
       $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       $request_condition=$this->build_where_protected($_array_set[0][0]);
       $request_set=$this->build_set_protected($_array_set[0][1]);
       $request_str="
         UPDATE `$table_name`
         SET $request_set[0] 
         WHERE $request_condition[0];
       ";
       $connexion_prepare=$connexion_pdo->prepare($request_str);
       $connexion_pdo->beginTransaction();
       foreach($_array_set as $set){
        $r1=$this->build_where_protected($set[0]);
        $r2=$this->build_set_protected($set[1]);
       	$r=Tool_array::array_merge_key($r1[1],$r2[1]);
        $connexion_prepare->execute($r);
       }
       $connexion_pdo->commit();
     }
     catch(PDOException $erreur){
      echo $erreur->getMessage();
     }
    }

    // array_value :
    // - clefs : nom_attribut
    // - valeurs : valeur à ajouter
    
    public function writer_values($_array_values){
     $table_name=$this->__get_table_name();
     $request=$this->build_insert_protected($_array_values);
     $request_str="INSERT INTO `$table_name` $request[0]";
     try{
       $connexion_pdo=$this->experience->__get_connexion_pdo();
	     $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	     $prepare_execute=$connexion_pdo->prepare($request_str);
       $prepare_execute->execute($request[1]);
       $res=$connexion_pdo->lastInsertId();
       $connexion_pdo = null; //Appel au destructeur de l'objet PDO
       return $res;
     }
     catch(PDOException $erreur){
      echo $erreur->getMessage();
     }
    }

    public function writer_array_values(&$_array_array_values){
     $table_name=$this->__get_table_name();
     try{
       $connexion_pdo=$this->experience->__get_connexion_pdo();
	     $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       $request=$this->build_insert_protected($_array_array_values[0]);
       $request_str="INSERT INTO `$table_name` $request[0]";
       $connexion_prepare=$connexion_pdo->prepare($request_str);
	     $connexion_pdo->beginTransaction();
       foreach($_array_array_values as $array_values){
        $r=$this->build_insert_protected($array_values);
        $connexion_prepare->execute($r[1]);
       }
       $connexion_pdo->commit();
     }
     catch(PDOException $erreur){
      echo $erreur->getMessage();
     }
    }

    public function reader_all2array_id(){
      $table_name=$this->__get_table_name();
      $id_name=$this->__get_primary_key_name();
      $array_res=array();
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="
          SELECT a.$id_name
          FROM `$table_name` a;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $prepare_execute->execute();
	      foreach($prepare_execute as $ligne){
	        $array_res[]=$ligne[0];
	      }
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $array_res;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }

    public function reader_id2array($_id){
      $table_name=$this->__get_table_name();
      $id_name=$this->__get_primary_key_name();
      $res;
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="SELECT * FROM `$table_name` WHERE $id_name=:$id_name;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $prepare_execute->execute(array(":$id_name"=>$_id));
        foreach($prepare_execute as $ligne){
          $res=$ligne;
        }
	      $connexion_pdo = null;
        return $res;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }

    public function reader_all2array($_where='',$_order_by=''){
      $table_name=$this->__get_table_name();
	    $array_res=array();      
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="SELECT * FROM `$table_name` $_where $_order_by;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $prepare_execute->execute();
	      foreach($prepare_execute as $ligne){
	        $array_res[]=$ligne;
	      }
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $array_res;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }

    public function reader_all2exec($_where='',$_order_by=''){
      $table_name=$this->__get_table_name();
	    $array_res=array();      
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="SELECT * FROM `$table_name` $_where $_order_by;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $prepare_execute->execute();
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
        return $prepare_execute;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }

    }

    public function writer_delete_table(){
      $table_name=$this->__get_table_name();
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "DELETE FROM `$table_name`;";
	      $connexion_pdo->exec($requete);
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

    public function writer_drop_table(){
      $table_name=$this->__get_table_name();
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "DROP TABLE IF EXISTS`$table_name`;";
	      $connexion_pdo->exec($requete);
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

    public function exec_query($_requete,$_array_protected){
      $table_name=$this->__get_table_name();
      $array_res=array();
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $prepare_execute = $connexion_pdo->prepare($_requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $prepare_execute->execute($_array_protected);
	      foreach($prepare_execute as $ligne){
	        $array_res[]=$ligne;
	      }
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $array_res;
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }


    public function exec_query2exec($_requete,$_array_protected){
      $table_name=$this->__get_table_name();
      $array_res=array();
      try{
        $connexion_pdo=$this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $prepare_execute = $connexion_pdo->prepare($_requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $prepare_execute->execute($_array_protected);
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $prepare_execute;
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

  }

  class sql_document extends sql_object{
    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	      CREATE TABLE `Document`(
	        id_document int primary key auto_increment,
	        path_doc varchar(255),
	        lg varchar(3),
          contenu blob
    	  );";
	      $connexion_pdo->exec($requete);
        $requete='
          CREATE INDEX `indexDocumentLg`
          on `Document` (lg)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }


    public function reader_path2array($_path){
      $requete = 'SELECT * FROM `Document` WHERE path_doc LIKE \''.$_path.'\';';
      $pro = array(':path'=>$_path);
      return $this->exec_query($requete,$pro);
    }

    /*
     * Pour une langue lg, récupérer tous les objets document
     */

    public function reader_lg2array($_lg){
      $requete='SELECT * FROM `Document` WHERE lg=:lg;';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    } 

    /*
     * Pour une langue lg, récupérer tous les id_document
     */

    public function reader_lg2array_id_document($_lg){
      $requete='
        SELECT d.id_document
        FROM `Document`d
        WHERE d.lg=:lg;';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    } 

    /*
     * Pour une langue lg, récupérer tous les id_document et le path associé
     */

    public function reader_lg2array_path_id_document($_lg){
      $requete='
        SELECT d.id_document, d.path_doc
        FROM `Document`d
        WHERE d.lg=:lg;';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    } 

    /*
     * Pour une langue lg, récupérer tous les id_documents et les
     * id_bloc associés
     */

    public function reader_lg2array_id_document_id_bloc($_lg){
      $requete='
        SELECT d.id_document, b.id_bloc
        FROM `Document`d, `Bloc` b
        WHERE d.lg=:lg
          AND d.id_document=b.id_document;
      ';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    } 

    /*
     * Pour une langue lg, récupérer tous les id_documents, path et les
     * id_bloc associés
     */

    public function reader_lg2array_id_document_path_id_bloc($_lg){
      $requete='
        SELECT d.id_document, d.path_doc, b.id_bloc
        FROM `Document`d, `Bloc` b
        WHERE d.lg=:lg
          AND d.id_document=b.id_document;
      ';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    } 

    /*
     * récupérer toutes les langues des différents documents stockés
     */

    public function reader_all_lg(){
      $requete='SELECT DISTINCT `lg` FROM `Document`;';
      return $this->exec_query($requete,array());
    }

  }

  class sql_bloc extends sql_object{

    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	        CREATE TABLE `Bloc`(
            id_bloc integer primary key auto_increment,
            id_document integer,
            contenu_text mediumblob,
            contenu_xhtml mediumblob
    	    );";
	      $connexion_pdo->exec($requete);
        $requete='
          CREATE INDEX `indexBlocDocument`
          on `Bloc` (id_document)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

//    public function 

    public function reader_lg2array($_lg){
      $requete='
        SELECT b.id_bloc,b.contenu_text
        FROM `Document` d, `Bloc` b
        WHERE d.lg=:lg
          AND d.id_document=b.id_document;
      ';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    }

    public function reader_lg2id_bloc($_lg){
      $requete='
        SELECT b.id_bloc,b.id_document
        FROM `Document` d, `Bloc` b
        WHERE d.lg=:lg
          AND d.id_document=b.id_document;
      ';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    }

    
/*
    public function reader_lg2id_bloc($_lg){
      $requete='
        SELECT b.id_bloc,b.id_document
        FROM `Document` d, `Bloc` b
        WHERE d.lg=:lg
          AND d.id_document=b.id_document;
      ';
      $pro=array(':lg'=>$_lg);
      return $this->exec_query($requete,$pro);
    }
*/

    public function reader_id_document2array($_id_document){
      $requete='
        SELECT b.id_bloc,b.contenu_text
        FROM `Document` d, `Bloc` b
        WHERE d.id_document=:id_document
          AND d.id_document=b.id_document;
      ';
      $pro=array(':id_document'=>$_id_document);
      return $this->exec_query($requete,$pro);
    }

    public function reader_id_document2id_bloc($_id_document){
      $requete='
        SELECT b.id_bloc
        FROM `Document` d, `Bloc` b
        WHERE d.id_document=:id_document
          AND d.id_document=b.id_document;
      ';
      $pro=array(':id_document'=>$_id_document);
      return $this->exec_query($requete,$pro);
    }

    public function reader_all2exec_plus_lg(){
      $requete='
        SELECT b.id_bloc,b.contenu_text,d.lg
        FROM `Document` d, `Bloc` b
        WHERE d.id_document=b.id_document;
      ';
      $pro=array();
      return $this->exec_query($requete,$pro);

    } 

  }

  class sql_graphie extends sql_object{
    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	        CREATE TABLE `Graphie`(
	          id_graphie integer primary key auto_increment,
            len integer,
	          graphie varchar(255)
    	    );";
	      $connexion_pdo->exec($requete);
        $requete='
          CREATE INDEX `indexGraphie`
          on `Graphie` (graphie)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

    public function reader_graphie2id_graphie($_graphie){
      $requete='
        SELECT g.id_graphie
        FROM `Graphie` g
        WHERE g.graphie=:graphie;
      ';
      $pro=array(':graphie'=>$_graphie);
      return $this->exec_query($requete,$pro);
    }

    public function reader_id_document2graphie($_id_doc){
      $requete="
        SELECT g.graphie
        FROM `Graphie` g, `Word` r,`Bloc` b
        WHERE g.id_graphie = r.id_graphie
          AND r.id_bloc = b.id_bloc
          AND b.id_document = :id_document
        ORDER BY r.id_bloc,r.offset
      ;";
      //$prepare_execute=$this->exec_query2exec($requete,array('id_document'=>$_id_doc));
      $prepare_execute=$this->exec_query($requete,array('id_document'=>$_id_doc));
      return $prepare_execute;
    }

    public function reader_lg_graphie2repartition_bloc($_lg,$_graphie,$_array_corres){
      $requete="
        SELECT r.id_graphie,r.id_bloc,COUNT(r.id_bloc)
        FROM `Word` r,`Bloc` b, `Document` d, `Graphie` g
        WHERE r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
          AND r.id_graphie = g.id_graphie
          AND g.graphie = :graphie
        GROUP BY r.id_bloc,r.id_graphie
        ORDER BY r.id_graphie,r.id_bloc
      ;";

      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg,'graphie'=>$_graphie));
      $array=array("$_graphie"=>array());
      $str="";
      foreach($prepare_execute as $ligne){
        $relative_id_bloc = $_array_corres[$_lg][$ligne[1]];
        $array[$_graphie][$relative_id_bloc] = $ligne[2];
      }
      $prepare_execute = null;
      return $array;
    }

    public function reader_lg_graphie2repartition_doc($_lg,$_graphie,$_array_corres){
      $requete="
        SELECT r.id_graphie,d.id_document,COUNT(d.id_document)
        FROM `Word` r,`Bloc` b, `Document` d, `Graphie` g
        WHERE r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
          AND r.id_graphie = g.id_graphie
          AND g.graphie = :graphie
        GROUP BY d.id_document,r.id_graphie
        ORDER BY r.id_graphie,d.id_document
      ;";

      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg,'graphie'=>$_graphie));
      $array=array("$_graphie"=>array());
      $str="";
      foreach($prepare_execute as $ligne){
        $relative_id_bloc = $_array_corres[$_lg][$ligne[1]];
        $array[$_graphie][$relative_id_bloc] = $ligne[2];
      }
      $prepare_execute = null;
      return $array;
    }

  }
 

  class sql_rstr extends sql_object{
    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	        CREATE TABLE `Rstr`(
	          id_rstr integer primary key auto_increment,
	          id_bloc integer,
	          id_graphie integer,
	          offset integer
    	    );";
	      $connexion_pdo->exec($requete);

        $requete='
          CREATE INDEX `indexRstrBloc`
          on `Rstr` (id_bloc)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

        $requete='
          CREATE INDEX `indexRstrGraphie`
          on `Rstr` (id_graphie)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

    public function reader_lg2array($_lg){
      $ap = array('lg'=>$_lg);

      $requete="
        SELECT r.id_graphie,g.graphie,COUNT(b.id_document)
        FROM `Rstr` r,`Bloc` b, `Document` d, `Graphie` g
        WHERE g.id_graphie=r.id_graphie
          AND r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        GROUP BY r.id_bloc,r.id_graphie
        ORDER BY r.id_graphie,r.id_bloc
      ;";

      $pe = $this->exec_query($requete,$ap);
      return $pe;
    }


/*
    public function reader_all2count_bloc(){
      $array_res=array();
      try{
        $connexion_pdo = new PDO("sqlite:".$this->file_name_sqlite);
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="
          SELECT g.graphie,r.id_graphie,r.id_bloc,COUNT(r.id_bloc)
          FROM `Rstr` r,`Graphie` g
          WHERE r.id_graphie=g.id_graphie
          GROUP BY r.id_bloc,r.id_graphie
          ORDER BY r.id_graphie,r.id_bloc
        ;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $prepare_execute->execute();
        $array=array();
        $str="";
	      foreach($prepare_execute as $ligne){
          if(isset($old_ligne) and $old_ligne[1]!=$ligne[1]){
            $str=rtrim($str,"|");
            $array[$str][]=array($old_ligne[0],$old_ligne[1]);
            $str="$ligne[2]:$ligne[3]|";
          }
          else{
            $str.="$ligne[2]:$ligne[3]|";
          }
          $old_ligne=$ligne;
        }
        $str=rtrim($str,"|");
        $array[$str][]=$old_ligne[0];
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $array;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }
*/

    public function reader_seg2seg_inside($_id_bloc,$o,$l){
      $ap = array('id_bloc'=>$_id_bloc);
      $e = $o + $l - 1;

      $req = "
        SELECT r.id_rstr, r.id_graphie, r.offset, g.graphie, g.len
        FROM `Rstr` r, `Graphie` g
        WHERE r.id_bloc = '$_id_bloc'
          AND g.id_graphie = r.id_graphie
          AND (     (r.offset < $o AND (r.offset + g.len - 1) > $o)
                OR  (r.offset > $o AND r.offset <= $e)
                OR  ((r.offset + g.len - 1) > $e AND r.offset <= $e)
              )
      ;";
/*
      $req = "
        SELECT r.id_rstr, r.id_graphie, r.offset, g.graphie, g.len
        FROM `Rstr` r, `Graphie` g
        WHERE r.id_bloc = '$_id_bloc'
          AND g.id_graphie = r.id_graphie
          AND r.offset <= $end;
      ";
*/
      $pe=$this->exec_query($req,$ap);
      return $pe;

    }

    public function reader_lg_id_graphie2array_id_rstr($_lg,$_id_graphie){
      $ap = array('lg'=>$_lg,'id_graphie' => $_id_graphie);
      $req = "
        SELECT r.id_rstr, r.offset, b.id_bloc, d.id_document 
        FROM `Document` d, `Rstr` r, `Bloc` b
        WHERE r.id_graphie = :id_graphie
          AND r.id_bloc = b.id_bloc
          AND b.id_document = d.id_document
          AND d.lg = :lg;
      ";
      $pe = $this->exec_query($req,$ap);
      return $pe;
    }

    public function reader_lg_graphie2array_id_rstr($_lg,$_graphie){
//        FROM `Graphie` g, `Rstr` r, `Bloc` b, `Document` d
//          AND r.id_bloc = b.id_bloc
//          AND b.id_document = d.id_document
//          AND d.lg = :lg;
      $requete = "
        SELECT g.id_graphie, g.graphie, g.len
        FROM `Graphie` g
        WHERE g.graphie LIKE '$_graphie';
      ";
      $array_prep = array('graphie'=>$_graphie);
      $prepare_execute=$this->exec_query2exec($requete,$array_prep);
      $return = array();
      foreach($prepare_execute as $ligne){
        $res = $this->reader_lg_id_graphie2array_id_rstr($_lg,$ligne[0]);
        $ret = array();
        $ret[0] = array('id_graphie' => $ligne[0], 'graphie' =>$ligne[1], 'len'=>$ligne[2]);
        $ret[1] = array();
        $flag = false;
        foreach($res as $r){
          $flag = true;
          $ret[1][]= array(
            'id_rstr' => $r[0], 'offset' => $r[1],'id_bloc' => $r[2], 'id_document' => $r[3]
          );
        }
        if($flag) $return[] = $ret;
      }
      return $return;
    }

    /*
     * A partir d'une graphie, récupérer toutes les langues dont
     * la graphie en question est utilisée
     */

    public function reader_graphie2array_lg($_graphie){
      $req = "
        SELECT DISTINCT d.lg
        FROM `Document` d, `Rstr` r, `Bloc` b, `Graphie` g
        WHERE g.graphie LIKE '$_graphie'
          AND r.id_graphie = g.id_graphie
          AND r.id_bloc = b.id_bloc
          AND b.id_document = d.id_document
        ;
      ";
      $ap = array('graphie'=>$_graphie);
      $pe = $this->exec_query($req,$ap);
      return $pe;
    }

    public function reader_id_doc2relative_id_bloc($_id_doc){
      $requete="
        SELECT b.id_bloc
        FROM `Bloc` b
        WHERE b.id_document=:id_document;
      ";
      $prepare_execute=$this->exec_query2exec($requete,array('id_document'=>$_id_doc));
      $cpt=0;
      $array_corres=array();
      foreach($prepare_execute as $ligne){
        $array_corres[$_id_doc][$ligne[0]] = ++$cpt;
      }
      return $array_corres;
    }


    public function reader_all2relative_id_bloc($_lg){
      $requete="
        SELECT b.id_bloc
        FROM `Bloc` b,`Document` d
        WHERE b.id_document=d.id_document
          AND d.lg=:lg;
      ";
      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));

      $cpt=0;
      $array_corres=array();
      foreach($prepare_execute as $ligne){
        $array_corres[$_lg][$ligne[0]]=++$cpt;
      }
      return $array_corres;
    }


    public function reader_all2relative_id_doc($_lg){
      $requete="
        SELECT d.id_document
        FROM `Document` d
        WHERE d.lg=:lg;
      ";
      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));

      $cpt=0;
      $array_corres=array();
      foreach($prepare_execute as $ligne){
        $array_corres[$_lg][$ligne[0]]=++$cpt;
      }
      return $array_corres;
    }


    /*
     * Pour une langue lg, avoir toutes les graphies stockées associées à
     * cette langue
     */

    public function reader_lg2distinct_id_graphie($_lg){
      $requete="
        SELECT DISTINCT r.id_graphie
        FROM `Rstr` r,`Bloc` b, `Document` d
        WHERE r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        ORDER BY r.id_graphie
      ;";
      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      return $prepare_execute;
    }

    /*
     * Pour une langue lg, avoir la répartition de ses graphies sur les blocs
     */

    public function reader_all2count_bloc($_lg){
      $requete="
        SELECT r.id_graphie,g.graphie,g.len,r.id_bloc,COUNT(r.id_bloc)
        FROM `Rstr` r,`Bloc` b, `Document` d, `Graphie` g
        WHERE g.id_graphie=r.id_graphie
          AND r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        GROUP BY r.id_bloc,r.id_graphie
        ORDER BY r.id_graphie,r.id_bloc
      ;";

      return $this->exec_query2exec($requete,array('lg'=>$_lg));
    }



    public function reader_all2count_bloc2(&$_array_res,$_lg,$_array_corres){
      $requete="
        SELECT r.id_graphie,r.id_bloc,COUNT(r.id_bloc)
        FROM `Rstr` r,`Bloc` b, `Document` d
        WHERE r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        GROUP BY r.id_bloc,r.id_graphie
        ORDER BY r.id_graphie,r.id_bloc
      ;";

      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      $array=array();
      $str="";
      foreach($prepare_execute as $ligne){
        $relative_id_bloc=$_array_corres[$_lg][$ligne[1]];
        if(isset($old_ligne) and $old_ligne[0]!=$ligne[0]){
          $str=rtrim($str,"|");
          $_array_res[$str][]=array($old_ligne[0],$_lg);
          $str="$relative_id_bloc:$ligne[2]|";
        }
        else{
          $str.="$relative_id_bloc:$ligne[2]|";
        }
        $old_ligne=$ligne;
      }
      $str=rtrim($str,"|");
      $_array_res[$str][]=array($old_ligne[0],$_lg);
      $prepare_execute = null;
    }

    public function reader_all2count_doc(&$_array_res,$_lg,$_array_corres){
      $requete="
        SELECT r.id_graphie,b.id_document,COUNT(b.id_document)
        FROM `Rstr` r,`Bloc` b, `Document` d
        WHERE r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        GROUP BY b.id_document,r.id_graphie
        ORDER BY r.id_graphie,b.id_document
      ;";

      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      $array=array();
      $str="";
      foreach($prepare_execute as $ligne){
//        print_r($ligne);
//        die();
        $relative_id_bloc=$_array_corres[$_lg][$ligne[1]];
        if(isset($old_ligne) and $old_ligne[0]!=$ligne[0]){
          $str=rtrim($str,"|");
          $_array_res[$str][]=array($old_ligne[0],$_lg);
          $str="$relative_id_bloc:$ligne[2]|";
        }
        else{
          $str.="$relative_id_bloc:$ligne[2]|";
        }
        $old_ligne=$ligne;
      }
      $str=rtrim($str,"|");
      $_array_res[$str][]=array($old_ligne[0],$_lg);
      $prepare_execute = null;
    }

    public function reader_lg2count_doc($_lg){
      $requete="
        SELECT DISTINCT w.id_rstr
        FROM `Rstr` w, `Bloc` b, `Document` d
        WHERE w.id_bloc = b.id_bloc
          AND b.id_document = d.id_document
          AND d.lg = :lg
      ;";
      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      $cpt = 0;
      foreach($prepare_execute as $ligne){
        ++$cpt;
      }
      return $cpt;
    }


/*
    public function reader_graphie2array($_graphie,$_where="",$_order_by=""){
      $array_res=array();
      try{
        $connexion_pdo = new PDO("sqlite:".$this->file_name_sqlite);
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="
          SELECT r.offset,r.len,r.id_bloc
          FROM `Rstr` r,`Graphie` g
          WHERE g.graphie=:graphie
            AND r.id_graphie=g.id_graphie
          $_order_by;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $tab_valeurs=array(':graphie'=>$_graphie);
	      $prepare_execute->execute($tab_valeurs);
	      foreach($prepare_execute as $ligne){
          $array_res[]=array($ligne['offset'],$ligne['len'],$ligne['id_bloc']);
        }
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $array_res;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }


    public function reader_graphie2count_bloc($_graphie,$_where="",$_order_by=""){
      $array_res=array();
      try{
        $connexion_pdo = new PDO("sqlite:".$this->file_name_sqlite);
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="
          SELECT r.id_bloc,COUNT(r.id_bloc)
          FROM `Rstr` r,`Graphie` g
          WHERE g.graphie=:graphie
            AND r.id_graphie=g.id_graphie
          GROUP BY r.id_bloc
          $_order_by;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $tab_valeurs=array(':graphie'=>$_graphie);
	      $prepare_execute->execute($tab_valeurs);
	      foreach($prepare_execute as $ligne){
          $array_res[$ligne[0]]=$ligne[1];
        }
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $array_res;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }

    public function reader_id2count_bloc($_id_graphie,$_where="",$_order_by=""){
      $array_res=array();
      try{
        $connexion_pdo = new PDO("sqlite:".$this->file_name_sqlite);
        $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $requete="
          SELECT r.id_bloc,COUNT(r.id_bloc)
          FROM `Rstr` r,`Graphie` g
          WHERE g.id_graphie=:id_graphie
            AND r.id_graphie=g.id_graphie
          GROUP BY r.id_bloc
          $_order_by;";
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	      $tab_valeurs=array(':id_graphie'=>$_id_graphie);
	      $prepare_execute->execute($tab_valeurs);
	      foreach($prepare_execute as $ligne){
          $array_res[$ligne[0]]=$ligne[1];
        }
	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
	      return $array_res;
      }
      catch(PDOException $erreur){
        echo $erreur->getMessage();
      }
    }
*/
  }

  class sql_word extends sql_rstr{
    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	        CREATE TABLE `Word`(
	          id_word integer primary key auto_increment,
	          id_bloc integer,
	          id_graphie integer,
	          offset integer
    	    );";
	      $connexion_pdo->exec($requete);

        $requete='CREATE INDEX `indexWordBloc` on `Word` (id_bloc);';
        $array_config = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY);
        $prepare_execute = $connexion_pdo->prepare($requete,$array_config);
        $prepare_execute->execute();

        $requete='CREATE INDEX `indexWordGraphie` on `Word` (id_graphie);';
        $array_config = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY);
        $prepare_execute = $connexion_pdo->prepare($requete,$array_config);
        $prepare_execute->execute();

	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

    public function reader_id_bloc2info_word($_id_bloc){
      $requete="
        SELECT r.id_word, r.offset, g.len, g.graphie 
        FROM `Word` r, `Graphie` g
        WHERE r.id_bloc=:id_bloc
          AND r.id_graphie = g.id_graphie
        ORDER BY r.id_bloc,r.offset
      ;";
      $prepare_execute=$this->exec_query($requete,array('id_bloc'=>$_id_bloc));
      return $prepare_execute;
    }

    public function reader_id_word2graphie($_id_word){
      $requete="
        SELECT g.graphie
        FROM `Word` r, `Graphie` g
        WHERE r.id_graphie = g.id_graphie
          AND r.id_word = :id_word
      ;";
      $prepare_execute=$this->exec_query2exec($requete,array('id_word'=>$_id_word));
      foreach($prepare_execute as $ligne){
        return $ligne[0];
      }
    }

    /*
     * Pour une langue lg, avoir toutes les graphies stockées associées à
     * cette langue
     */

    public function reader_lg2distinct_id_graphie($_lg){
      $requete="
        SELECT DISTINCT r.id_graphie
        FROM `Word` r,`Bloc` b, `Document` d
        WHERE r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        ORDER BY r.id_graphie
      ;";
      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      return $prepare_execute;
    }

    /*
     * Pour une langue lg, avoir la répartition de ses graphies sur les blocs
     */

    public function reader_all2count_bloc($_lg){
      $requete="
        SELECT r.id_graphie,g.graphie,g.len,r.id_bloc,COUNT(r.id_bloc)
        FROM `Word` r,`Bloc` b, `Document` d, `Graphie` g
        WHERE g.id_graphie=r.id_graphie
          AND r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        GROUP BY r.id_bloc,r.id_graphie
        ORDER BY r.id_graphie,r.id_bloc
      ;";

      return $this->exec_query2exec($requete,array('lg'=>$_lg));
    }

    public function reader_lg2count_doc($_lg){
      $requete="
        SELECT DISTINCT w.id_word
        FROM `Word` w, `Bloc` b, `Document` d
        WHERE w.id_bloc = b.id_bloc
          AND b.id_document = d.id_document
          AND d.lg = :lg
      ;";
      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      $cpt = 0;
      foreach($prepare_execute as $ligne){
        ++$cpt;
      }
      return $cpt;
    }

    public function reader_all2count_bloc2(&$_array_res,$_lg,$_array_corres){
      $requete="
        SELECT r.id_graphie,r.id_bloc,COUNT(r.id_bloc),g.graphie,r.id_word
        FROM `Word` r,`Bloc` b, `Document` d, `Graphie` g
        WHERE r.id_bloc=b.id_bloc
          AND r.id_graphie=g.id_graphie
          AND b.id_document=d.id_document
          AND d.lg=:lg
        GROUP BY r.id_bloc,r.id_graphie
        ORDER BY r.id_graphie,r.id_bloc
      ;";

      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      $array=array();
      $str="";
      foreach($prepare_execute as $ligne){
        $relative_id_bloc=$_array_corres[$_lg][$ligne[1]];
        if(isset($old_ligne) and $old_ligne[0]!=$ligne[0]){
          $str=rtrim($str,"|");
          $_array_res[$str][]=array($old_ligne[0],$_lg,$old_ligne[3],$old_ligne[4]);
          $str="$relative_id_bloc:$ligne[2]|";
        }
        else{
          $str.="$relative_id_bloc:$ligne[2]|";
        }
        $old_ligne=$ligne;
      }
      $str=rtrim($str,"|");
      $_array_res[$str][]=array($old_ligne[0],$_lg,$old_ligne[3],$old_ligne[4]);
      $prepare_execute = null;
    }

    public function reader_id_doc2count_bloc(&$_array_res,$_id_doc,$_lg,$_array_corres){
      $requete="
        SELECT r.id_graphie,r.id_bloc,COUNT(r.id_bloc),g.graphie,r.id_word
        FROM `Word` r,`Bloc` b, `Graphie` g
        WHERE r.id_graphie = g.id_graphie
          AND r.id_bloc=b.id_bloc
          AND b.id_document=:id_document
        GROUP BY r.id_bloc,r.id_graphie
        ORDER BY r.id_graphie,r.id_bloc
      ;";

      $prepare_execute=$this->exec_query2exec($requete,array('id_document'=>$_id_doc));
      $array=array();
      $str="";
      foreach($prepare_execute as $ligne){
        $relative_id_bloc=$_array_corres[$ligne[1]];
        if(isset($old_ligne) and $old_ligne[0]!=$ligne[0]){
          $str=rtrim($str,"|");
          $_array_res[$str][$_lg][]=array($old_ligne[0],$_lg,$old_ligne[3],$old_ligne[4]);
          $str="$relative_id_bloc:$ligne[2]|";
        }
        else{
          $str.="$relative_id_bloc:$ligne[2]|";
        }
        $old_ligne=$ligne;
      }
      $str=rtrim($str,"|");
      $_array_res[$str][$_lg][]=array($old_ligne[0],$_lg,$old_ligne[3],$old_ligne[4]);
      $prepare_execute = null;
    }


    public function reader_all2count_doc(&$_array_res,$_lg,$_array_corres){
      $requete="
        SELECT r.id_graphie,b.id_document,COUNT(b.id_document)
        FROM `Word` r,`Bloc` b, `Document` d
        WHERE r.id_bloc=b.id_bloc
          AND b.id_document=d.id_document
          AND d.lg=:lg
        GROUP BY b.id_document,r.id_graphie
        ORDER BY r.id_graphie,b.id_document
      ;";

      $prepare_execute=$this->exec_query2exec($requete,array('lg'=>$_lg));
      $array=array();
      $str="";
      foreach($prepare_execute as $ligne){
//        print_r($ligne);
//        die();
        $relative_id_bloc=$_array_corres[$_lg][$ligne[1]];
        if(isset($old_ligne) and $old_ligne[0]!=$ligne[0]){
          $str=rtrim($str,"|");
          $_array_res[$str][]=array($old_ligne[0],$_lg);
          $str="$relative_id_bloc:$ligne[2]|";
        }
        else{
          $str.="$relative_id_bloc:$ligne[2]|";
        }
        $old_ligne=$ligne;
      }
      $str=rtrim($str,"|");
      $_array_res[$str][]=array($old_ligne[0],$_lg);
      $prepare_execute = null;
    }

  }


  class sql_groupe extends sql_object{

    /*
     * type :
     * - 0 == document
     * - 1 == alinea
     */

    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	        CREATE TABLE `Groupe`(
	          id_groupe int,
	          id_graphie int,
	          id_lg varchar(3),
            type int
    	    );";
	      $connexion_pdo->exec($requete);

        $requete='
          CREATE INDEX `indexGroupeGraphie`
          on `Groupe` (id_graphie)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

        $requete='
          CREATE INDEX `indexGroupeLg`
          on `Groupe` (id_lg)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

    //array
    // [0]=>id_graphie
    // [1]=>lg

    public function reader_id_graphie_lg2id_groupe($_array){
      $requete="
        SELECT g.id_groupe
        FROM `Groupe` g
        WHERE g.id_graphie=:id_graphie
          AND g.id_lg=:id_lg;
      "; 
      $array_protected=array(
        'id_graphie'=>$_array[0],
        'id_lg'=>$_array[1]
      );
      $prepare_execute=$this->exec_query2exec($requete,$array_protected);
      foreach($prepare_execute as $ligne){
        return $ligne[0];
      }
    }

    //array
    // [0]=>id_graphie
    // [1]=>lg
    // [2]=>type

    public function reader_id_graphie_lg_type2id_groupe($_array){
      $requete="
        SELECT g.id_groupe
        FROM `Groupe` g
        WHERE g.id_graphie=:id_graphie
          AND g.id_lg=:id_lg
          AND g.type=:type;
      "; 
      $array_protected=array(
        'id_graphie'=>$_array[0],
        'id_lg'=>$_array[1],
        'type'=>$_array[2]
      );
      $prepare_execute=$this->exec_query2exec($requete,$array_protected);
      foreach($prepare_execute as $ligne){
        return $ligne[0];
      }
    }


/*
    public function reader_all2array_id_group_id_obj(){
      $requete="
        SELECT grp.id_groupe, min(grp.id_graphie), grp.id_lg
        FROM `Groupe` grp
        WHERE grp.id_lg = (
          SELECT min(grp2.id_lg)
          FROM `Groupe` grp2
          WHERE grp.id_groupe=grp2.id_groupe
        )
        GROUP BY grp.id_groupe";
      $requete="
        SELECT *
        FROM `Groupe` g
        ORDER BY g.id_groupe,g.id_graphie,g.id_lg;
      ";
      $prepare_execute=$this->exec_query2exec($requete,array());
      echo "[!]\n";
      $old_ligne=array(0,0,"");
	    foreach($prepare_execute as $ligne){
        if($old_ligne[0]<$ligne[0] and $old_ligne[0]!=0){
          $array_res[]=array($old_ligne[0],$old_ligne[1],$old_ligne[2]);
        }
        $old_ligne=$ligne;
	    }
	    return $array_res;
    }
*/
  } 

  class sql_comparaison extends sql_object{
    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	        CREATE TABLE `Comparaison`(
	          id_groupe1 int,
	          id_groupe2 int,
            cos float,
            jac float,
            im float
    	    );";
//            alinea_t_score float,
//            alinea_dice float,
	      $connexion_pdo->exec($requete);
        $requete='
          CREATE INDEX `indexComparaisonGroupe1`
          on `Comparaison` (id_groupe1)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

        $requete='
          CREATE INDEX `indexComparaisonGroupe2`
          on `Comparaison` (id_groupe2)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }
  } 


  class sql_chunk extends sql_object{
    public function writer_create_table(){
      try{
        $connexion_pdo = $this->experience->__get_connexion_pdo();
	      $connexion_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	      $requete = "
	        CREATE TABLE `Chunk`(
	          id_chunk int,
	          id_word int,
            type varchar(2)
    	    );";
//            alinea_t_score float,
//            alinea_dice float,
	      $connexion_pdo->exec($requete);
        $requete='
          CREATE INDEX `indexChunk`
          on `Chunk` (id_chunk)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

        $requete='
          CREATE INDEX `indexWord`
          on `Chunk` (id_word)
        ;';
        $prepare_execute = $connexion_pdo->prepare($requete,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $prepare_execute->execute();

	      $connexion_pdo = null; //Appel au destructeur de l'objet PDO
      }
      catch(PDOException $erreur){
	      echo $erreur->getMessage();
      }
    }

    public function reader_id_word2info_type($_id_word){
      $requete="
        SELECT c.type
        FROM `Chunk` c
        WHERE c.id_word = :id_word;
      ";
      $res = array();
      $prepare_execute=$this->exec_query($requete,array('id_word'=>$_id_word));
      foreach($prepare_execute as $ligne){
        return $ligne[0];
      }
    }

    public function reader_id_bloc2info_chunk($_id_bloc){
      $requete="
        SELECT c.id_chunk, r.id_word, r.offset, g.len, g.graphie, c.type 
        FROM `Word` r, `Graphie` g, `Chunk` c
        WHERE r.id_bloc=:id_bloc
          AND r.id_word = c.id_word
          AND g.id_graphie = r.id_graphie
        ORDER BY c.id_chunk,r.offset
      ;";
      $res = array();
      $prepare_execute=$this->exec_query($requete,array('id_bloc'=>$_id_bloc));

      $array_chunk = array();
      $chunk = array();
      foreach($prepare_execute as $ligne){
        $mot = array($ligne[1],$ligne[2],$ligne[3],$ligne[4],$ligne[5]);
        if(!isset($last_id_chunk)){
          $last_id_chunk = $ligne['id_chunk'];
        }
        if($ligne['id_chunk'] == $last_id_chunk){
          $chunk[$ligne['id_chunk']][]=$mot;
        }
        else{
          $chunk[$ligne['id_chunk']]=array($mot);
        }
        $last_id_chunk = $ligne['id_chunk'];
      }

      return $chunk;
    }





  }

?>
